// 通用表同步工具
export async function getSourceDiyTable(sourceConn, tableName) {
  const sql = `SELECT * FROM diy_table WHERE Name = ?`;
  const [rows] = await sourceConn.query(sql, [tableName]);
  if (rows.length === 0) {
    throw new Error(`源库不存在表 ${tableName}`);
  }
  return rows[0];
}

export async function syncDiyTableMetadata(sourceConn, targetConn, sourceTable, tableName) {
  const sql = `SELECT * FROM diy_table WHERE Name = ?`;
  const [rows] = await targetConn.query(sql, [tableName]);
  console.log("rows", rows);
  // 1. 获取源表和目标表字段
  const [srcCols] = await sourceConn.query("SHOW COLUMNS FROM diy_table");
  const [tgtCols] = await targetConn.query("SHOW COLUMNS FROM diy_table");
  const tgtColNames = tgtCols.map((col) => col.Field);

  // 2. 补充目标表缺失字段
  for (const srcCol of srcCols) {
    if (!tgtColNames.includes(srcCol.Field)) {
      const addSql = `ALTER TABLE diy_table ADD COLUMN \`${srcCol.Field}\` ${srcCol.Type} ${srcCol.Null === "NO" ? "NOT NULL" : ""} ${
        srcCol.Default !== null ? `DEFAULT '${srcCol.Default}'` : ""
      }`;
      console.log(`补充目标库 diy_table 字段: ${srcCol.Field}`);
      await targetConn.query(addSql);
      console.log(`➕ [syncDiyTableMetadata] 补充目标库 diy_table 字段: ${srcCol.Field}`);
    }
  }

  // 3. 正常插入/更新逻辑
  const fields = Object.keys(sourceTable);
  const values = fields.map((f) => sourceTable[f]);
  if (rows.length > 0) {
    const setClause = fields.map((f) => `\`${f}\` = ?`).join(", ");
    const updateSql = `UPDATE diy_table SET ${setClause} WHERE Name = ?`;
    // console.log('[syncDiyTableMetadata][UPDATE] SQL:', updateSql);
    // console.log('[syncDiyTableMetadata][UPDATE] fields:', fields);
    // console.log('[syncDiyTableMetadata][UPDATE] values:', values);
    await targetConn.query(updateSql, [...values, tableName]);
    console.log(`✅ 更新 diy_table 源数据`);
    return rows[0];
  } else {
    const placeholders = fields.map(() => "?").join(", ");
    const escapedFields = fields.map((f) => `\`${f}\``).join(", ");
    const insertSql = `INSERT INTO diy_table (${escapedFields}) VALUES (${placeholders})`;
    console.log("[syncDiyTableMetadata][INSERT] SQL:", insertSql);
    // console.log('[syncDiyTableMetadata][INSERT] fields:', fields);
    // console.log('[syncDiyTableMetadata][INSERT] values:', values);
    await targetConn.query(insertSql, values);
    console.log(`✅ 插入 diy_table 源数据`);
    return sourceTable;
  }
  // console.log(`已同步 diy_table: ${tableName}`, rows);
  // return rows[0];
}

export async function checkTargetTableExists(targetConn, tableName) {
  const [rows] = await targetConn.query(`SHOW TABLES LIKE ?`, [tableName]);
  //   console.log(`检查目标表 ${tableName} 是否存在: ${rows.length > 0}`, rows);
  return rows.length > 0;
}

export async function createTableInTarget(sourceConn, targetConn, tableName) {
  const [result] = await sourceConn.query(`SHOW CREATE TABLE \`${tableName}\``);
  if (!result.length) throw new Error(`无法获取 ${tableName} 结构`);
  const createSQL = result[0]["Create Table"];
  // 获取目标数据库 targetConn 中的字符集和排序规则
  const [targetCharsetResult] = await targetConn.query("SHOW VARIABLES LIKE 'character_set_database'");
  const [targetCollationResult] = await targetConn.query("SHOW VARIABLES LIKE 'collation_database'");
  const targetCharset = targetCharsetResult[0].Value;
  const targetCollation = targetCollationResult[0].Value;

  // 全局替换所有字段和表的字符集/排序规则
  let finalSQL = createSQL
    .replace(/CHARACTER SET\s+\w+/gi, `CHARACTER SET ${targetCharset}`)
    .replace(/COLLATE\s+\w+/gi, `COLLATE ${targetCollation}`)
    .replace(/DEFAULT CHARSET=[^ ]+/gi, `DEFAULT CHARSET=${targetCharset}`)
    .replace(/COLLATE=[^ ]+/gi, `COLLATE=${targetCollation}`);
  console.log(`创建目标表 SQL: ${finalSQL}`);
  await targetConn.query(finalSQL);
  console.log(`✅ 创建目标表 ${tableName}`);
}

export async function syncTableDataOnce(sourceConn, targetConn, tableName, removeField = false) {
  if (!removeField) {
    console.log(`🔄 跳过数据迁移（removeField = false）`);
    return;
  }
  const [sourceData] = await sourceConn.query(`SELECT * FROM \`${tableName}\``);

  // 清空目标表
  await targetConn.query(`TRUNCATE TABLE \`${tableName}\``);
  console.log(`🗑️ 清空目标表 ${tableName} 数据`);

  if (sourceData.length === 0) {
    console.log(`⚠️ 源库表 ${tableName} 无数据`);
    return;
  }

  const dataFields = Object.keys(sourceData[0]);
  const fieldList = dataFields.map((f) => `\`${f}\``).join(", ");
  const placeholders = dataFields.map(() => "?").join(", ");
  const insertSql = `INSERT INTO \`${tableName}\` (${fieldList}) VALUES (${placeholders})`;

  const insertPromises = sourceData.map((row) => {
    const values = dataFields.map((f) => row[f]);
    return targetConn.query(insertSql, values);
  });

  await Promise.all(insertPromises);
  console.log(`✅ 已迁移 ${sourceData.length} 条数据到 ${tableName}`);
}

export async function compareAndSyncTableSchema(sourceConn, targetConn, tableName, isDeleteRedundant) {
  const [srcCols] = await sourceConn.query(`SHOW COLUMNS FROM \`${tableName}\``);
  const [tgtCols] = await targetConn.query(`SHOW COLUMNS FROM \`${tableName}\``);

  const tgtMap = {};
  tgtCols.forEach((col) => (tgtMap[col.Field] = col));

  for (const src of srcCols) {
    if (!tgtMap[src.Field]) {
      const addSql = `ALTER TABLE \`${tableName}\` ADD COLUMN \`${src.Field}\` ${src.Type} ${src.Null === "NO" ? "NOT NULL" : ""} ${
        src.Default !== null ? `DEFAULT '${src.Default}'` : ""
      }`;
      await targetConn.query(addSql);
      console.log(`➕ 新增列 ${src.Field}`);
    } else {
      const tgt = tgtMap[src.Field];
      if (src.Type !== tgt.Type || src.Null !== tgt.Null || src.Default !== tgt.Default) {
        const modifySql = `ALTER TABLE \`${tableName}\` MODIFY COLUMN \`${src.Field}\` ${src.Type} ${src.Null === "NO" ? "NOT NULL" : ""} ${
          src.Default !== null ? `DEFAULT '${src.Default}'` : ""
        }`;
        await targetConn.query(modifySql);
        console.log(`✏️ 修改列 ${src.Field}`);
      }
    }
  }
  if (isDeleteRedundant) {
    console.log(`🔄 开始删除多余列`);
    // 删除多余列（可选）
    for (const tgt of tgtCols) {
      if (!srcCols.find((c) => c.Field === tgt.Field)) {
        const dropSql = `ALTER TABLE \`${tableName}\` DROP COLUMN \`${tgt.Field}\``;
        await targetConn.query(dropSql);
        console.log(`❌ 删除列 ${tgt.Field}`);
      }
    }
  }

  console.log(`✅ 表结构已同步`);
}
